package com.work.dao;

import com.work.bean.Student;
import com.work.bean.StudentDTO;
import com.work.utils.DruidManger;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {

    //创建QueryRunner 对象启用连接池
    QueryRunner qr = new QueryRunner(DruidManger.getDataSource());

    @Override
    public int addStu(Student stu) throws Exception {
        String sql = " insert into Student values(? , ? , ? , ?) ";
        int i = qr.update(sql, stu.getS_id(), stu.getS_name(), stu.getS_birth(), stu.getS_sex());
        return i;
    }

    @Override
    public int uptStu(Student stu) throws Exception {
        String sql = " update student set s_name = ?  where s_id = ? ";
        int i = qr.update(sql, stu.getS_name(), stu.getS_id());
        return i;
    }

    @Override
    public int delStu(int sid) throws Exception {
        String sql = " delete from student where s_id = ? ";
        int i = qr.update(sql, sid);
        return i;
    }

    @Override
    public List<Student> selectAll() throws Exception {
        String sql = " select * from student ";
        //new BeanListHandler<实体名>(实体名.class)
        //new ColumnListHandler<>() 放字段
        return qr.query(sql, new BeanListHandler<Student>(Student.class));
    }

    @Override
    public List<String> findSnameList() throws Exception {
        String sql = " select s_name from student ";
        return qr.query(sql, new ColumnListHandler<String>());
    }

    @Override
    public Student findStuById(String sid) throws Exception {
        String sql = " select * from student where s_id = ? ";
        return qr.query(sql, new BeanHandler<Student>(Student.class), sid);
    }

    @Override
    public int findCount() throws Exception {
        String sql = " select count(1) from student ";
        Long count = qr.query(sql, new ScalarHandler<>());
        //lon.intValue():把Long类型转化为int类型
        return count.intValue();
    }

    @Override
    public List<Student> findPramStu(String sid, String sname, String ssex) throws Exception {
        String sql = " select * from student where 1=1 ";
        //创建集合，保存值
        List<Object> prams = new ArrayList<>();
        if (sid != null && !sid.equals("")) {
            sql += " and s_id like ? ";
            prams.add("%" + sid + "%");
        }
        if (sname != null && !sname.equals("")) {
            sql += " and s_name = ? ";
            prams.add(sname);
        }
        if (ssex != null && !ssex.equals("")) {
            sql += " and s_sex = ? ";
            prams.add(ssex);
        }
        //toArray把数组转换集合
        List<Student> list = qr.query(sql, new BeanListHandler<Student>(Student.class), prams.toArray());
        return list;
    }

    @Override
    public List<Student> findScoreStu(StudentDTO studentDTO) throws Exception {
        String sql = " select * from student s, score c where s.`s_id` = c.`s_id` ";
        int startScore = studentDTO.getStartScore();
        int endScore = studentDTO.getEndScore();
        List list = new ArrayList();
        if(startScore > 0){
            sql += " and c.`s_score`>= ? ";
            list.add(startScore);
        }
        if (endScore < 100){
            sql += " and c.`s_score`>= ? ";
            list.add(endScore);
        }
        return qr.query(sql,new BeanListHandler<Student>(Student.class),list.toArray());
    }
}
